package com.example.dao;

import com.example.model.pojo.Competition;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;

/**
 * @Description //TODO
 * @Date 2022/9/20
 * @Author cxy
 **/
@Transactional
public interface CompetitionDao extends JpaRepository<Competition,Integer> {
    @Query(value = "select * from competition where start_date>now() and status=0",nativeQuery = true)
    List<Competition> findAll();

    @Modifying
    @Query(value = "update competition set status=1 where start_date<=now() and end_date>now() and status=0",nativeQuery = true)
    void updateStatus();

    @Query(value = "select cid from competition where start_date<=now() and end_date>now() and status=1",nativeQuery = true)
    List<Integer> findActiveIds();

    @Modifying
    @Query(value = "update competition set status=2 where end_date<now() and status=1",nativeQuery = true)
    void updateEndStatus();

    @Query(value = "select id from competition where end_date<now() and status=2",nativeQuery = true)
    List<Integer> findEndIds();

    @Query(value ="select start_date from competition where id=?",nativeQuery = true)
    Date startDate(Integer id);

    @Query(value = "select end_date from competition where id=?",nativeQuery = true)
    Date endDate(Integer id);
}
